// $Id$
/*
 *  Copyright (C) 2007 Noury Solutions
 *
 *  This library is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU Lesser General Public
 *  License as published by the Free Software Foundation; either
 *  version 3 of the License, or (at your option) any later version.
 *
 *  This library is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *  Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public
 *  License along with this library. If not, see <http://www.gnu.org/licenses/>.
 *
 */

using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
#if (!NET_CF)
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Runtime.ConstrainedExecution;
#endif

namespace Northwind.DomainModel.Data
{

    public class NorthwindDb:
#if (!NET_CF)
        CriticalFinalizerObject,
#endif
        IDisposable
    {
        public NorthwindDb(NorthwindDatabaseType type)
        {
            _DatabaseType=type;

            string fileExt=null;
            string resName=null;
#if (!NET_CF)
            DbConnectionStringBuilder conStrBuilder=null;
#else
            StringBuilder conStrBuilder=null;
#endif
            Type conType=null;

            switch (type)
            {
#if (!NET_CF)
            case NorthwindDatabaseType.Access:
                fileExt="mdb";
                resName="Northwind.Resources.Northwind.mdb";
                conStrBuilder=new OleDbConnectionStringBuilder();
                ((OleDbConnectionStringBuilder)conStrBuilder).Provider="Microsoft.Jet.OLEDB.4.0";
                ((OleDbConnectionStringBuilder)conStrBuilder).DataSource="{0}";
                ((OleDbConnectionStringBuilder)conStrBuilder).PersistSecurityInfo=false;
                conStrBuilder["Mode"]="ReadWrite";
                conType=typeof(OleDbConnection);
                break;
            case NorthwindDatabaseType.SqlClient:
                fileExt="mdf";
                resName="Northwind.Resources.Northwind.mdf";
                conStrBuilder=new SqlConnectionStringBuilder();
                ((SqlConnectionStringBuilder)conStrBuilder).DataSource=@".\SQLEXPRESS";
                ((SqlConnectionStringBuilder)conStrBuilder).AttachDBFilename="{0}";
                ((SqlConnectionStringBuilder)conStrBuilder).Enlist=true;
                ((SqlConnectionStringBuilder)conStrBuilder).IntegratedSecurity=true;
                ((SqlConnectionStringBuilder)conStrBuilder).Pooling=false;
                conType=typeof(SqlConnection);
                break;
            case NorthwindDatabaseType.SqlCompact:
                fileExt="sdf";
                resName="Northwind.Resources.Northwind.sdf";
                conStrBuilder=new DbConnectionStringBuilder();
                conStrBuilder["Data Source"]="{0}";
                conType=typeof(SqlCeConnection);
                break;
#endif
            case NorthwindDatabaseType.SQLite:
                fileExt="db";
                resName="Northwind.Resources.Northwind.db";
#if (!NET_CF)
                conStrBuilder=new SQLiteConnectionStringBuilder();
                ((SQLiteConnectionStringBuilder)conStrBuilder).DataSource="{0}";
#else
                conStrBuilder=new StringBuilder("data source={0}");
#endif
                conType=typeof(SQLiteConnection);
                break;
            }

            _TempFileCollection=new TempFileCollection(
#if (!NET_CF)
                Environment.GetEnvironmentVariable("SystemRoot")+@"\Temp", // SqlClient cannot attach a file in the local user directory (%TEMP%)
                false
#endif
            );
            _Filename=_TempFileCollection.AddExtension(fileExt, false);
            using (BinaryWriter bw=new BinaryWriter(File.Open(_Filename, FileMode.Create)))
            {
                Stream source=Assembly.GetExecutingAssembly().GetManifestResourceStream(resName);
                byte [] buffer=new byte[0x10000];
                int bytes;
                try
                {
                    while ((bytes=source.Read(buffer, 0, buffer.Length))>0)
                        bw.Write(buffer, 0, bytes);
                } finally
                {
                    bw.Flush();
                }
            }

            _Connection=(DbConnection)conType.GetConstructor(
                new Type[] {
                    typeof(string)
                }
            ).Invoke(
                new object [] {
                    string.Format(
                        CultureInfo.InvariantCulture,
                        conStrBuilder.ToString(),
                        _Filename
                    )
                }
            );
        }

        ~NorthwindDb()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (_Connection!=null)
            {
                _Connection.Close();
                _Connection.Dispose();

#if (!NET_CF)
                // Clear the pool so that we can delete the .mdf file
                SqlConnection sqlCnx=_Connection as SqlConnection;
                if (sqlCnx!=null)
                {
                    try
                    {
                        SqlConnection.ClearAllPools();
                        using (SqlConnection cnx=new SqlConnection(@"Data Source=.\SQLEXPRESS;Database=master;Integrated Security=True"))
                        {
                            cnx.Open();
                            using (SqlCommand cmd=cnx.CreateCommand())
                            {
                                cmd.CommandText="sp_detach_db";
                                cmd.CommandType=CommandType.StoredProcedure;

                                SqlParameter p1=new SqlParameter("@dbname", SqlDbType.NVarChar);
                                p1.Value=_Filename;

                                SqlParameter p2=new SqlParameter("@skipchecks", SqlDbType.NVarChar);
                                p2.Value="true";

                                SqlParameter p3=new SqlParameter("@keepfulltextindexfile", SqlDbType.NVarChar);
                                p3.Value="false";

                                cmd.Parameters.Add(p1);
                                cmd.Parameters.Add(p2);
                                cmd.Parameters.Add(p3);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    } catch
                    {
                    }
                }
#endif
            }
            if (_TempFileCollection!=null)
                _TempFileCollection.Delete();
        }

        public DbConnection Connection
        {
            get
            {
                return _Connection;
            }
        }

        public NorthwindDatabaseType DatabaseType
        {
            get
            {
                return _DatabaseType;
            }
        }

        private TempFileCollection _TempFileCollection;
        private string _Filename;
        private DbConnection _Connection;
        private NorthwindDatabaseType _DatabaseType;
    }

    internal static class Utils
    {
        public static int GetLastId(IDbConnection connection, IList<IDbCommand> commands)
        {
            string selectId=Resources.Sql.SqlServerSelectId;

            Type factoryType=connection.GetType();
            if (factoryType==typeof(SQLiteConnection))
                selectId=Resources.Sql.SQLiteSelectId;

            IDbCommand c=connection.CreateCommand();
            c.CommandType=CommandType.Text;
            c.CommandText=selectId;
            c.Transaction=commands[0].Transaction;

            return Convert.ToInt32(c.ExecuteScalar());
        }
    }
}
